package dao;

import domain.Department;
import domain.School;
import domain.Teacher;
import exception.BysjException;
import service.SchoolService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DepartmentDao {
    private static DepartmentDao departmentDao = null;
    //将唯一构造器设置成私有，阻止在本类外生成本类的对象
    public static DepartmentDao getInstance(){
        //静态变量SchoolDao为null时，才创建SchoolDao对象
        //第二次或后来调用本方法就不指向null了，就不在创建对象。
        if (departmentDao == null){
            departmentDao = new DepartmentDao();
        }
        return departmentDao;
    }
    public Department find(int id)throws SQLException {
        //创建School类型的引用，暂时不创建对象
        Department desiredU = null;
        //获得数据库的连接

        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "select * from department where id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的元组，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int school_id = resultSet_select_id.getInt("school_id");
            School school = SchoolService.getInstance().find(school_id);
            desiredU = new Department(id,description,no,remarks,school);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredU;
    }
    public static boolean add(Connection connection, Department department) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_insert = "insert into department " +
                "(description,no,remarks,id) values (?,?,?,?)";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1,department.getDescription());
        pstmt_insert.setString(2,department.getNo());
        pstmt_insert.setString(3,department.getRemarks());
        pstmt_insert.setInt(4,department.getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
    public static boolean update(Connection connection, Department department) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_update = "update department " +
                "set description,no,remarks,id=? where id=2";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
        pstmt_update.setString(1,department.getDescription());
        pstmt_update.setString(2,department.getNo());
        pstmt_update.setString(3,department.getRemarks());
        pstmt_update.setInt(4,department.getSchool().getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();
        return rowAffected > 0;
    }
    public boolean delete(int id) throws SQLException{
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句中的空格
        PreparedStatement preparedStatement = connection.prepareStatement("delete from department where id=?");
        //对预编译语句对象的参数赋值
        preparedStatement.setInt(1,id);
        //执行预编译语句，用其返回值、影响的行数赋值给
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        return affectedRowNum>0;
    }
}
